/******************************************************;
*This do file combines the Census counts of nurses and cleans up the geographic codes in order to merge with the other data;

******************************************************/

capture clear
clear matrix
clear mata
capture macro drop _all
set mem 1000m
set more off

#delimit ;

***SET DIRECTORY STRUCTURE***;
global data 
global nscb 
******************************************************;
use "${data}Census_1990_nurse.dta", clear;
append using "${data}Census_2000_nurse.dta";
destring prv, replace;
append using "${data}Census_2010_nurse.dta";

*Merge in province names;
merge n:1 prv using "${nscb}nscb_prov_codes.dta";

*Reassign to original provinces before provincial divisons;
replace province="ZAMBOANGA DEL SUR" if province=="ZAMBOANGA SIBUGAY";											
replace province="SURIGAO DEL NORTE" if province=="DINAGAT ISLANDS"|province=="85";

replace province="SOUTH COTABATO" if province=="SARANGANI"	;					
replace province="DAVAO DEL NORTE" if province=="COMPOSTELA VALLEY";

replace province="KALINGA" if province=="APAYAO";
				
*Merging all districts of Manila into one;
replace province="THIRD DISTRICT" if province=="FIRST DISTRICT"	;				
replace province="THIRD DISTRICT" if province=="SECOND DISTRICT";			 
replace province="THIRD DISTRICT" if province=="FOURTH DISTRICT";

collapse (sum) nurse nurse_midwife health_degree health_degree_nurse health_degree_nurse_mid
	sci_engineering teaching other_prof clerks sales, by(year province_full);

rename province_full province;

rename nurse nurse_cen;

save "${data}Census_nurse_counts.dta", replace;
